package goodenglish.infrastructure.repository.db;

import goodenglish.domain.model.lexicon.NewWord;
import goodenglish.domain.model.lexicon.StudyInfo;
import goodenglish.domain.model.lexicon.StudyPlan;
import goodenglish.domain.model.studyplan.StudyPlanRepository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StudyPlanRepositoryDb implements StudyPlanRepository {
	@Override
	public void addStudyPlan(final String lexicon_id, final StudyPlan studyPlan) {
		Connection conn = null;
		PreparedStatement prep1 = null;
		PreparedStatement prep2 = null;
		try {
			conn = DBUtil.getConnection();

			prep1 = conn.prepareStatement("insert into StudyPlan(id,name,lexicon_id) values(?,?,?);");
			prep1.setString(1, studyPlan.getId());
			prep1.setString(2, studyPlan.getName());
			prep1.setString(3, lexicon_id);
			prep1.executeUpdate();
			DBUtil.beginBatch(conn);
			prep2 = conn.prepareStatement("update NewWord set studyPlan_id=? where id=?");
			for (StudyInfo studyInfo : studyPlan.getStudyInfos()) {
				prep2.setString(1, studyPlan.getId());
				prep2.setString(2, studyInfo.getId());
				prep2.addBatch();
			}

			prep2.executeBatch();
			DBUtil.commitBatch(conn);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeStatement(prep1, prep2);
			DBUtil.closeConnection(conn);
		}

	}

	@Override
	public void loadStudyInfos(final StudyPlan studyPlan) {
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;
		try {

			conn = DBUtil.getConnection();
			prep = conn
					.prepareStatement("select id,spell,explain,exampleSentStr,rightCount from NewWord where studyPlan_id=?;");
			prep.setString(1, studyPlan.getId());
			rs = prep.executeQuery();
			List<StudyInfo> studyWords = new ArrayList<StudyInfo>();
			while (rs.next()) {
				NewWord newWord = new NewWord();
				newWord.setId(rs.getString("id"));
				newWord.setSpell(rs.getString("spell"));
				newWord.setExplain(rs.getString("explain"));
				newWord.setExampleSentStr(rs.getString("exampleSentStr"));
				StudyInfo studyInfo = new StudyInfo();
				studyInfo.setNewWord(newWord);
				studyInfo.setId(newWord.getId());
				studyInfo.setRightCount(rs.getInt("rightCount"));
				studyWords.add(studyInfo);

			}
			studyPlan.setStudyInfos(studyWords);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closeStatement(prep);
			DBUtil.closeConnection(conn);
		}
	}

	@Override
	public void updateNewWordRightCount(final StudyInfo newWord) {
		Connection conn = null;
		PreparedStatement prep = null;
		try {
			conn = DBUtil.getConnection();

			prep = conn.prepareStatement("update NewWord set rightCount=? where id=?");
			prep.setInt(1, newWord.getRightCount());
			prep.setString(2, newWord.getId());
			prep.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("更新正确次数出错", e);
		} finally {
			DBUtil.closeStatement(prep);
			DBUtil.closeConnection(conn);
		}

	}
}
